﻿<cfsilent>
	<cfsetting requesttimeout="300" />
	<cfflush interval="1024" />
	<cfscript>
		
		termId = event.getArg("TermID");

		propAdvice = getProperty("serviceFactory").getBean("cetEntryPropertyAdvice");

		temFile = GetTempDirectory() & createUUID() & ".xls";
		cetUpdate = 0;
		cetInsert = 0;
		
		targetArgs = structNew();
		structInsert(targetArgs, "TabID", "labDownload", true);
		
	</cfscript>
</cfsilent>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>导入CET收费数据</title>
<link href="<cfoutput>#getProperty('cdnServer')#</cfoutput><cfoutput>#getContextRoot()#</cfoutput>/acadmicRes/assets/css/impoter.css" rel="stylesheet" type="text/css">
</head>

<body>

	<cfif isDefined("FORM.upload")>
		
		<!--- 将文件保存至临时目录 --->
		<cftry>
			<cffile action="upload" fileField="upload" destination="#temFile#" nameconflict="overwrite">
			<cfcatch type="application">
				<p class="error">上传的文件类型不正确, 请使用电子表格作为数据导入文件.</p>
			</cfcatch>
		</cftry>
		
		<!--- 将文件转换为电子表格对象 --->
		<cfif fileExists(temFile)>
			<cftry>
				<cfspreadsheet action="read" src="#temFile#" excludeHeaderRow="true" headerrow="1" query="rs" sheetname="CET收费明细">
				<cfcatch type="application">
					<p class="error">电子表格文件格式不正确, 请使用下载的模板文件录入CET收费信息</p>
				</cfcatch>
			</cftry>

			<!--- 文件已成功转换为查询对象 --->
			<cfif isDefined("rs") and isQuery(rs)>
			
				<p class="success">尝试读取电子表格数据 ... 完成</p>
				
				<!--- 检查必要数据字段 --->
				<cfset columnChecked = true />
				<cfif not listFindNoCase(rs.columnList, "学号")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "姓名")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "报考科目")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "金额")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "缴费方式")><cfset columnChecked = false /></cfif>
				
				<cfif not columnChecked>
					<p class="error">电子表格数据列不完整, 请检查模板文件是否包含以下必须字段信息: 学号,姓名,报考科目,金额,缴费方式</p>
				</cfif>
				
				<cfif columnChecked>

					<p class="success">正在检查数据子项 ... 完成</p> 
					
					<cftry>
						
						<cftransaction>
							
							<cfloop query="rs">
								
								<!--- 检查考试种类 --->
								<cfscript>

									sql = "SELECT exam_charge FROM t_cet WHERE exam_id = :examId ";

									queryObj = new Query( datasource=application.dnsMaster );
									queryObj.addParam( name="examId", value=rs['报考科目'][rs.currentRow], cfsqltype="cf_sql_char" );

									rs_cet = queryObj.execute( sql=sql ).getResult();

								</cfscript>
								
								<cfif rs_cet.recordCount >

										<!--- 检查学号 --->
										<cfscript>
											
											sql = "SELECT stu_name FROM t_student WHERE stu_id = :studentId ";

											queryObj = new Query( datasource=application.dnsMaster );
											queryObj.addParam( name="studentId", value=rs['学号'][rs.currentRow], cfsqltype="cf_sql_varchar" );

											rs_student = queryObj.execute( sql=sql ).getResult();
											
										</cfscript>

										<cfif rs_student.recordCount >
												
												<!--- 检查报考信息 --->
												<cfscript>
													
													sql = "SELECT eid, entry_prop FROM t_cet_entry WHERE stu_id = :studentId AND exam_id = :examId AND term_id = :termId ";
													
													queryObj = new Query( datasource=application.dnsMaster );
													queryObj.addParam( name="studentId", value=rs['学号'][rs.currentRow], cfsqltype="cf_sql_varchar" );
													queryObj.addParam( name="examId", value=rs['报考科目'][rs.currentRow], cfsqltype="cf_sql_char" );
													queryObj.addParam( name="termId", value=termId, cfsqltype="cf_sql_char" );

													rs_entry = queryObj.execute( sql=sql ).getResult();
													
												</cfscript>

												<cfif rs_entry.recordCount >
														
														<!--- 检查学号与姓名信息匹配 --->
														<cfif rs_student.stu_name eq rs['姓名'][rs.currentRow] >
																
																<!--- 检查缴费金额匹配 --->
																<cfif rs_cet.exam_charge eq rs['金额'][rs.currentRow]>
																			
																			<!--- 检查缴费方式 --->
																			<cfif listFind( "现金 / 银行代扣 / 网银", rs['缴费方式'][rs.currentRow], "/ ") >

																					<!--- 更新缴费状态 --->
																					<cfscript>
																					
																						propAdvice.parseProperty( rs_entry.entry_prop );
																						propAdvice.setLocked( true );
																						
																						switch ( rs['缴费方式'][rs.currentRow] ) {
																							
																							case "现金":
																								propAdvice.setCash( true );
																								propAdvice.setBankInstead( false );
																								propAdvice.setBankOnline( false );
																								break;
																							
																							case "银行代扣":
																								propAdvice.setCash( false );
																								propAdvice.setBankInstead( true );
																								propAdvice.setBankOnline( false );
																								break;
																							
																							case "网银":
																								propAdvice.setCash( false );
																								propAdvice.setBankInstead( false );
																								propAdvice.setBankOnline( true );
																								break;
																							
																						}
																						
																						propStr = propAdvice.stringProperty();
																					
																						sql = "UPDATE t_cet_entry 
																								SET 
																									is_charged = '1', 
																									entry_prop = :propStr 
																								WHERE 
																									eid = :entryId ";
																						
																						queryObj = new Query( datasource=application.dnsMaster );
																						queryObj.addParam( name="entryId", value=rs_entry.eid, cfsqltype="cf_sql_varchar" );
																						queryObj.addParam( name="propStr", value=propStr, cfsqltype="cf_sql_varchar" );
																						
																						queryObj.execute( sql=sql );
																					
																					</cfscript>

																					<cfset cetUpdate++ />
																					
																				<cfelse>
																					<p class="error">学号 <cfoutput>#rs['学号'][rs.currentRow]#</cfoutput> 报考科目 <cfoutput>#rs['报考科目'][rs.currentRow]#</cfoutput> 缴费方式 <cfoutput>#rs['缴费方式'][rs.currentRow]#</cfoutput> 不正确</p>
																			</cfif>
																			
																		<cfelse>
																			<p class="error">学号 <cfoutput>#rs['学号'][rs.currentRow]#</cfoutput> 报考科目 <cfoutput>#rs['报考科目'][rs.currentRow]#</cfoutput> 应缴费金额不匹配</p>
																</cfif>
																
															<cfelse>
																<p class="error">学号 <cfoutput>#rs['学号'][rs.currentRow]#</cfoutput> 姓名 <cfoutput>#rs['姓名'][rs.currentRow]#</cfoutput> 不匹配</p>
														</cfif>
														
													<cfelse>
														<p class="error">学号 <cfoutput>#rs['学号'][rs.currentRow]#</cfoutput> 报考科目 <cfoutput>#rs['报考科目'][rs.currentRow]#</cfoutput> 在系统中没有报考记录</p>
												</cfif>

											<cfelse>
												<p class="error">学号 <cfoutput>#rs['学号'][rs.currentRow]#</cfoutput> 在系统中没有匹配记录</p>
										</cfif>
								
									<cfelse>
										<p class="error">报考科目 <cfoutput>#rs['报考科目'][rs.currentRow]#</cfoutput> 在系统中没有匹配记录</p>
								</cfif>
								
							</cfloop>
						
						</cftransaction>
						
						<cfcatch type="any">
							<p class="error">数据导入错误... <cfoutput>#cfcatch.Message#</cfoutput></p>
						</cfcatch>
					</cftry>
					
				</cfif>
				
			</cfif>
			
			
		</cfif> 
		
		<p>更新现有成绩数据 [<cfoutput>#cetUpdate#</cfoutput>] 条.</p>
		
		<a target="_parent" href="<cfoutput>#buildURL('cetState', targetArgs)#</cfoutput>">刷新CET成绩数据</a>
		
		<!--- 删除临时文件 --->
		<cfif fileExists(temFile)>
			<cffile action="delete" file="#temFile#" />		
		</cfif>
	
	</cfif>

</body>
</html>
